干饭FFing

sql oracle 函数

函数可以写真select、where、order by 后

大小写函数 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
--原始表
SQL> select * from test;
        ID NAME                        AGE    AVG DT
---------- -------------------- ---------- ------ -----------
         1 a                            11   1.10 2024/1/1
         2 b                            22  22.32 2023/1/1
         3 c                            33  33.33 2023/2/12
         4 SA_aa                         4        2024/12/23
         5 SA_bbb                        5        2123/2/23
         7                                        2024/3/3
         8 SB                           23        2012/2/23
         6 SA_ccc                        6        2003/3/3

--范例,将name字段转为小写
SQL> select lower(name) from test;
LOWER(NAME)
--------------------
a
b
c
sa_a
sa_b
sb
sa_c

--将name字段转为大写 
SQL> select upper(name) from test;
UPPER(NAME)
--------------------
A
B
C
SA_A
SA_B
SB
SA_C

--将name字段首字母大写，其他小写
SQL> select initcap(name) from test;
INITCAP(NAME)
--------------------
A
B
C
Sa_Aa
Sa_Bbb
Sb
Sa_Ccc



--where字段name为小写
SQL> select * from test where lower(name) ='sb';
        ID NAME                        AGE    AVG DT
---------- -------------------- ---------- ------ -----------
         8 SB                           23        2012/2/23

--where字段值转为小写(注：where =右侧值一般是变量)
SQL> select * from test where lower(name) =lower('Sb');
        ID NAME                        AGE    AVG DT
---------- -------------------- ---------- ------ -----------
         8 SB                           23        2012/2/23

字符串处理函数

dual（惰）表被称为伪表，是因为select语句在from时必须依赖于一个存在的表，而dual表就在select处理字符串时可以直接使用，仅仅是满足select from的语法格式。 

1
2
3
4
5
--concat,连接函数，将2个字符串连接在一起，和'||'功能相同
SQL> select concat ('abc','!@#') from dual;
CONCAT('ABC','!@#')
-------------------
abc!@#

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
--SUBSTR,字符串切片，对字符串直接切片
--语法：substr('字符串'，位置，长度)
--注！！！若长度位置省略，则是从位置到末尾  
--注！！！substr，截取的顺序永远是从左到右，哪怕是负数也是从左到右

SQL> select substr('2024-01-08',1,4) from dual;
SUBSTR('2024-01-08',1,4)
------------------------
2024

--从左向右第6位字符开始，向右截取2位
SQL> select substr('2024-01-08',6,2) from dual;
SUBSTR('2024-01-08',6,2)
------------------------
01

--从左右向右第6位字符，向右至末尾
SQL> select substr('2024-01-08',6) from dual;
SUBSTR('2024-01-08',6)
----------------------
01-08





--SUBSTR,切片为0，则打印全部字符串，从test表字段获取；
SQL> select substr(DT,0) from test;
SUBSTR(DT,0)
------------------------------------------------------------------------
01-JAN-24
01-JAN-23
12-FEB-23
23-DEC-24
23-FEB-23
03-MAR-24
23-FEB-12
03-MAR-03
8 rows selected


--倒数第5位开始，向右截取2位
SQL> select substr('2024-01-08',-5,2) from dual;
SUBSTR('2024-01-08',-5,2)
-------------------------
01

--SUBSTR,切片为负数则从右向左数字符，-1为最后一个字符，-2为倒数第二个
--从test表字段获取DT字段，从倒数第2位开始值末尾字符
SQL> select substr(DT,-2) from test;
SUBSTR(DT,-2)
-------------
24
23
23
24
23
24
12
03
8 rows selected

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
--length获取字符串长度，对字符串直接获取
SQL> select length('123') from dual;
LENGTH('123')
-------------
            3

--length，获取字段列的长度，从从test表字段获取；
SQL> select * from test;
        ID NAME                        AGE    AVG DT
---------- -------------------- ---------- ------ -----------
         1 a                            11   1.10 2024/1/1
         2 b                            22  22.32 2023/1/1
         3 c                            33  33.33 2023/2/12
         4 SA_aa                         4        2024/12/23
         5 SA_bbb                        5        2123/2/23
         7                                        2024/3/3
         8 SB                           23        2012/2/23
         6 SA_ccc                        6        2003/3/3
8 rows selected

SQL> select length(NAME) from test;
LENGTH(NAME)
------------
           1
           1
           1
           5
           6
           2
           6
8 rows selected

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
--instr,查找字符串所在位置，区分大小写
--语法：instr ('字符串'，'要查找的字符串'，'从第几位开始'，'第几次出现')
--查找c出现的位置
SQL> select instr ('abcdabcd','c') from dual;
INSTR('ABCDABCD','C')
---------------------
                    3
--查找c第二次出现的位置
SQL> select instr ('abcdabcd','c','1','2') from dual;
INSTR('ABCDABCD','C','1','2')
-----------------------------
                            7                    


--当确定字符位置时也可以使用like _ %来匹配
--例：查找name第4个字符为c的
SQL> select * from test;
        ID NAME                        AGE    AVG DT
---------- -------------------- ---------- ------ -----------
         1 a                            11   1.10 2024/1/1
         2 b                            22  22.32 2023/1/1
         3 c                            33  33.33 2023/2/12
         4 SA_aa                         4        2024/12/23
         5 SA_bbb                        5        2123/2/23
         7                                        2024/3/3
         8 SB                           23        2012/2/23
         9 SC_abcd                      34  12.00 
         6 SA_ccc                        6        2003/3/3
        10 ab                           19  19.00
--使用like配合3个_ 和%
SQL> select * from test where name like '___c%';
        ID NAME                        AGE    AVG DT
---------- -------------------- ---------- ------ -----------
         6 SA_ccc                        6        2003/3/3

--使用函数instr定位字符c所在位置
SQL> select * from test where instr(name,'c')='4';
        ID NAME                        AGE    AVG DT
---------- -------------------- ---------- ------ -----------
         6 SA_ccc                        6        2003/3/3

--查找name中以ab开头的数据
SQL> select * from test where name like 'ab%';
        ID NAME                        AGE    AVG DT
---------- -------------------- ---------- ------ -----------
        10 ab                           19  19.00 

SQL> select * from test where instr(name,'ab')='1';
        ID NAME                        AGE    AVG DT
---------- -------------------- ---------- ------ -----------
        10 ab                           19  19.00 


--查找name不以ab开头的数据
SQL> select * from test where name not like 'ab%';
        ID NAME                        AGE    AVG DT
---------- -------------------- ---------- ------ -----------
         1 a                            11   1.10 2024/1/1
         2 b                            22  22.32 2023/1/1
         3 c                            33  33.33 2023/2/12
         4 SA_aa                         4        2024/12/23
         5 SA_bbb                        5        2123/2/23
         8 SB                           23        2012/2/23
         9 SC_abcd                      34  12.00 
         6 SA_ccc                        6        2003/3/3

SQL> select * from test where instr(name,'ab')='0';
        ID NAME                        AGE    AVG DT
---------- -------------------- ---------- ------ -----------
         1 a                            11   1.10 2024/1/1
         2 b                            22  22.32 2023/1/1
         3 c                            33  33.33 2023/2/12
         4 SA_aa                         4        2024/12/23
         5 SA_bbb                        5        2123/2/23
         8 SB                           23        2012/2/23
         6 SA_ccc                        6        2003/3/3

字符串填充 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
--LPAD，在字符串左侧填充，来达到所需长度，长度满足则忽略填充
--RPAD，在字符串右侧填充，来达到所需长度，长度满足则忽略填充
--语法： LPAD(字符串，长度，'填充的字符')

--在abc左侧填充字符"#"，使字符串满足5个长度
SQL> select lpad('abc',5,'#') from dual;
LPAD('ABC',5,'#')
-----------------
##abc

--在abc右侧填充字符"#"，使字符串满足5个长度
SQL> select rpad('abc',5,'#') from dual;
RPAD('ABC',5,'#')
-----------------
abc##

--将id列填充为2位数，左侧补充0
SQL> select * from test;
        ID NAME                        AGE    AVG DT
---------- -------------------- ---------- ------ -----------
         1 a                            11   1.10 2024/1/1
         2 b                            22  22.32 2023/1/1
         3 c                            33  33.33 2023/2/12
         4 SA_aa                         4        2024/12/23
         5 SA_bbb                        5        2123/2/23
         7                                        2024/3/3
         8 SB                           23        2012/2/23
         9 SC_abcd                      34  12.00 
         6 SA_ccc                        6        2003/3/3
        10 ab                           19  19.00 

SQL> select lpad(id,2,'0') from test;
LPAD(ID,2,'0')
--------------
01
02
03
04
05
07
08
09
06
10

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
--TRIM，截取字符，默认去掉两侧的字符串,方法一，不加参数
SQL> select trim('a' from 'abcd') from dual;
TRIM('A'FROM'ABCD')
-------------------
bcd

SQL> select trim('a' from 'abca') from dual;
TRIM('A'FROM'ABCA')
-------------------
bc


--TRIM，截取字符，默认去掉两侧的字符串,方法二，增加参数 both
SQL> select trim(both 'a' from 'abca') from dual;
TRIM(BOTH'A'FROM'ABCA')
-----------------------
bc



--去头
SQL> select trim(leading 'a' from 'abca') from dual;
TRIM(LEADING'A'FROM'ABCA')
--------------------------
bca


--去尾
SQL> select trim(trailing 'a' from 'abca') from dual;
TRIM(TRAILING'A'FROM'ABCA')
---------------------------
abc
图片预览
100%